package nway_dsr_db

import (
	"database/sql"
	"errors"
	"fmt"
	"nway_dsr/nway_dsr/utils/log"
	"nway_dsr/nway_dsr/utils/nway_db_connection"
	"strconv"
	"strings"
	"time"
)

type CDR struct {
	Uuid               string
	Bleg_uuid          sql.NullString //string
	Start_stamp        sql.NullString
	Answer_stamp       sql.NullString
	End_stamp          sql.NullString
	Duration           sql.NullString
	Caller_id_number   string
	Destination_number sql.NullString
	Billsec            string
	Record_file        sql.NullString //string
}

func QueryBlegCdr(uuid string) (error, CDR) {

	var sql string
	var cdr CDR
	sql = fmt.Sprintf("SELECT   uuid,bleg_uuid,  to_char(start_stamp,'yyyy-mm-dd HH24:MI:SS') as start_stamp, to_char(answer_stamp,'yyyy-mm-dd HH24:MI:SS') as answer_stamp, to_char(end_stamp,'yyyy-mm-dd HH24:MI:SS') as end_stamp,duration,caller_id_number,"+
		"destination_number,billsec,record_file  "+
		" FROM public.call_pg_cdr where uuid='%s';", uuid)
	logger.Debug("sql:%s\n", sql)
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	if err == nil {
		for rows.Next() {
			err = rows.Scan(&cdr.Uuid,
				&cdr.Bleg_uuid,
				&cdr.Start_stamp,
				&cdr.Answer_stamp,
				&cdr.End_stamp,
				&cdr.Duration,
				&cdr.Caller_id_number,
				&cdr.Destination_number,
				&cdr.Billsec,
				&cdr.Record_file)
			if err != nil {
				logger.Error(err)
			}
			return err, cdr
		}
		err = errors.New("not found cdr info")
	}
	return err, cdr
}
func QueryAlegCdr1Channel(uuid, other_uuid string) (error, CDR) {

	var sql string
	var cdr CDR
	sql = fmt.Sprintf("SELECT   uuid,bleg_uuid,  to_char(start_stamp,'yyyy-mm-dd HH24:MI:SS') as start_stamp, to_char(answer_stamp,'yyyy-mm-dd HH24:MI:SS') as answer_stamp, to_char(end_stamp,'yyyy-mm-dd HH24:MI:SS') as end_stamp,duration,caller_id_number,"+
		"destination_number,billsec,record_file  "+
		" FROM public.call_pg_cdr where uuid='%s' and length(record_file)>2 and  uuid not in (select bleg_uuid FROM public.call_pg_cdr where bleg_uuid='%s') ;", uuid, uuid)
	//logger.Debug("sql:%s\n", sql)
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	if err == nil {
		for rows.Next() {
			err = rows.Scan(&cdr.Uuid,
				&cdr.Bleg_uuid,
				&cdr.Start_stamp,
				&cdr.Answer_stamp,
				&cdr.End_stamp,
				&cdr.Duration,
				&cdr.Caller_id_number,
				&cdr.Destination_number,
				&cdr.Billsec,
				&cdr.Record_file)
			if err != nil {
				logger.Error(err)
			}
			return err, cdr
		}
		err = errors.New("not found cdr info")
	}
	return err, cdr
}
func QueryAlegCdr2Channel(uuid, other_uuid string) (error, CDR) {
	/*	SELECT   uuid,bleg_uuid, start_stamp,answer_stamp,end_stamp,duration,caller_id_number,
		        destination_number,billsec,record_file
				FROM public.call_pg_cdr where (uuid=%s and bleg_uuid=%s) or (bleg_uuid=%s and uuid=%s);


		, outbound_caller_id_number,
				     , context, , , ,
				     , , hangup_cause, uuid, , accountcode,
				     read_codec, write_codec, , direction, sip_hangup_disposition,
				     origination_uuid, sip_gateway_name, sip_term_status, sip_term_cause
				FROM public.call_pg_cdr where (uuid='1f232184-e62e-11e8-9d09-6f6819c8a509' and bleg_uuid='1d0a43b4-e62e-11e8-9cfd-6f6819c8a509') or (bleg_uuid='1f232184-e62e-11e8-9d09-6f6819c8a509' and uuid='1d0a43b4-e62e-11e8-9cfd-6f6819c8a509');
			以上是a leg,如果是b leg，则是bleg_uuid为空且uuid=其它记录的bleg_uuid
	*/
	var sql string
	var cdr CDR
	if len(other_uuid) > 1 {
		sql = fmt.Sprintf("SELECT   uuid,bleg_uuid,  to_char(start_stamp,'yyyy-mm-dd HH24:MI:SS') as start_stamp, to_char(answer_stamp,'yyyy-mm-dd HH24:MI:SS') as answer_stamp, to_char(end_stamp,'yyyy-mm-dd HH24:MI:SS') as end_stamp,duration,caller_id_number,"+
			"destination_number,billsec,record_file  "+
			" FROM public.call_pg_cdr where uuid='%s' and bleg_uuid='%s';", uuid, other_uuid)
	} else {
		sql = fmt.Sprintf("SELECT   uuid,bleg_uuid,  to_char(start_stamp,'yyyy-mm-dd HH24:MI:SS') as start_stamp, to_char(answer_stamp,'yyyy-mm-dd HH24:MI:SS') as answer_stamp, to_char(end_stamp,'yyyy-mm-dd HH24:MI:SS') as end_stamp,duration,caller_id_number,"+
			"destination_number,billsec,record_file  "+
			" FROM public.call_pg_cdr where uuid='%s' and length(record_file)>2 ;", uuid)
	}

	//logger.Debug("sql:%s\n", sql)
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	if err == nil {
		for rows.Next() {
			err = rows.Scan(&cdr.Uuid,
				&cdr.Bleg_uuid,
				&cdr.Start_stamp,
				&cdr.Answer_stamp,
				&cdr.End_stamp,
				&cdr.Duration,
				&cdr.Caller_id_number,
				&cdr.Destination_number,
				&cdr.Billsec,
				&cdr.Record_file)
			if err != nil {
				logger.Error(err)
			}
			return err, cdr
		}
		err = errors.New("not found cdr info")
	}
	return err, cdr
}
func QueryAndInsertCdr(uuid, other_uuid string) error {
	//logger.Debug(uuid, other_uuid)
	time.Sleep(3 * time.Second) //用于保证双向挂机
	var a_cdr, b_cdr CDR
	var err error
	err, a_cdr = QueryAlegCdr2Channel(uuid, other_uuid)
	if err != nil {
		//表示没找到a,b都有的记录
		logger.Debug(err)
		err, a_cdr = QueryAlegCdr1Channel(uuid, other_uuid)
		if err != nil {
			//表示a,b两路的都没找到
			logger.Debug(err)
			return err
		}
	}
	err, b_cdr = QueryBlegCdr(a_cdr.Bleg_uuid.String)
	a, b, s := selectScore(other_uuid)
	logger.Debug(a, b, s)
	if err != nil {
		//表示b没有记录
		logger.Debug(err)
		logger.Error(err)
		err = InsertDSRCdr(a_cdr.Uuid, a_cdr.Caller_id_number, a_cdr.Destination_number.String, a_cdr.Duration.String, a_cdr.Record_file.String,
			a_cdr.Start_stamp.String, a_cdr.End_stamp.String, a_cdr.Answer_stamp.String, "", a_cdr.Billsec, "0", "", "", other_uuid, a, b, s)
		if err != nil {
			logger.Debug(err)
			logger.Debug(a_cdr)
		}

	} else {
		err = InsertDSRCdr(a_cdr.Uuid, a_cdr.Caller_id_number, a_cdr.Destination_number.String, a_cdr.Duration.String, a_cdr.Record_file.String,
			a_cdr.Start_stamp.String, a_cdr.End_stamp.String, a_cdr.Answer_stamp.String, b_cdr.Answer_stamp.String, a_cdr.Billsec, b_cdr.Billsec, b_cdr.Caller_id_number, b_cdr.Destination_number.String, other_uuid, a, b, s)
		if err != nil {
			logger.Debug(err)
		}
	}

	return err
}
func InsertDSRCdr(call_id, caller, callee, duration, record_path, start_time, end_time, a_answer_time, b_answer_time, a_billsec, b_billsec, b_caller, b_callee, other_uuid, a_score, b_score, score string) error {
	var sql string

	sql = "INSERT INTO dsr_cdr (call_id,caller,callee,duration,record_path,start_time,end_time,a_answer_time,b_answer_time,a_billsec,b_billsec,b_caller,b_callee,other_uuid,a_score,b_score,score)" +
		" VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17);"

	//logger.Debug(sql)
	NwayConn := nwayconnection.NewDb()
	stmt, err := NwayConn.GetConn().Prepare(sql)
	defer stmt.Close()
	if err != nil {
		logger.Error(err)
		return err
	}
	if len(a_answer_time) < 5 && len(b_answer_time) < 5 {
		_, err = stmt.Exec(call_id, caller, callee, duration, record_path, start_time, end_time, nil, nil, a_billsec, b_billsec, b_caller, b_callee, other_uuid, a_score, b_score, score)
	} else if len(b_answer_time) < 5 {
		_, err = stmt.Exec(call_id, caller, callee, duration, record_path, start_time, end_time, a_answer_time, nil, a_billsec, b_billsec, b_caller, b_callee, other_uuid, a_score, b_score, score)

	} else if len(a_answer_time) > 5 && len(b_answer_time) > 5 {
		_, err = stmt.Exec(call_id, caller, callee, duration, record_path, start_time, end_time, a_answer_time, b_answer_time, a_billsec, b_billsec, b_caller, b_callee, other_uuid, a_score, b_score, score)

	}
	if err != nil {
		logger.Error(err)
		return err
	}
	return nil

}
func selectScore(other_uuid string) (string, string, string) {
	var sql string
	var a = 0
	var b = 0
	var s = 0
	weixian, youliang := keywordsLists()
	sql = "select fine,fail,callee from dsr_flow_content WHERE call_id='" + other_uuid + "';"
	logger.Debug("查询优良和危险词汇", sql)
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	if err == nil {
		for rows.Next() {
			var fail, fine, callee string
			rows.Scan(&fine, &fail, &callee)
			if fine != "" {
				for _, v := range strings.Split(fine, ",") {
					for _, v1 := range youliang {
						if v == v1["keyword"] {
							val, _ := v1["score"]
							logger.Debug(val, "==")
							s += val.(int)
							if callee == "true" { //b路
								b += val.(int)
							} else { //a路
								a += val.(int)
							}
							continue
						}
					}
				}
			}
			if fail != "" {
				for _, v := range strings.Split(fail, ",") {
					for _, v1 := range weixian {
						if v == v1["keyword"] {
							val, _ := v1["score"]
							logger.Debug(val, "--")
							s -= val.(int)
							if callee == "true" { //b路
								b -= val.(int)
							} else { //a路
								a -= val.(int)
							}
							continue
						}
					}
				}
			}
		}
	} else {
		logger.Debug("err selectScore:", err)
	}
	return strconv.Itoa(a), strconv.Itoa(b), strconv.Itoa(s)
}
func keywordsLists() ([]map[string]interface{}, []map[string]interface{}) {
	weixian := []map[string]interface{}{}
	lists := map[string]interface{}{}
	for _, v := range aiFlowContentGetWeixian() {
		var obj = map[string]interface{}{"keyword": v["keyword"], "score": v["score"]}
		weixian = append(weixian, obj)
	}
	lists["weixian"] = weixian
	youliang := []map[string]interface{}{}
	for _, v := range aiFlowContentGetYouliang() {
		var obj = map[string]interface{}{"keyword": v["keyword"], "score": v["score"]}
		youliang = append(youliang, obj)
	}
	lists["youliang"] = youliang
	return weixian, youliang
}

func aiFlowContentGetWeixian() []map[string]interface{} {
	var sql string
	sql = "select keyword,score from fail_keys;"
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	list := []map[string]interface{}{}
	if err == nil {
		for rows.Next() {
			var keyword, score = "", 0
			rows.Scan(&keyword, &score)
			list = append(list, map[string]interface{}{"keyword": keyword, "score": score})
		}
	}
	return list
}

func aiFlowContentGetYouliang() []map[string]interface{} {
	var sql string
	sql = "select keyword,score from fine_keys ;"
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	list := []map[string]interface{}{}
	if err == nil {
		for rows.Next() {
			var keyword, score = "", 0
			rows.Scan(&keyword, &score)
			list = append(list, map[string]interface{}{"keyword": keyword, "score": score})
		}
	}
	return list
}
func CheckSuccessKey(call_id, nway_flag, content string) error {
	var sql string
	sql = "select id from success_keys where '" + content + "' ~ keyword"
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	if err == nil {
		for rows.Next() {
			rows.Close()
			if nway_flag == "read" {
				sql = fmt.Sprintf("insert into success_info(call_id,content,is_read)values('%s','%s',True);", call_id, content)
			} else {
				sql = fmt.Sprintf("insert into success_info(call_id,content,is_read)values('%s','%s',False);", call_id, content)
			}
			logger.Debug(sql)
			NwayConn = nwayconnection.NewDb()
			stmt, err := NwayConn.GetConn().Prepare(sql)
			defer stmt.Close()
			if err != nil {
				logger.Error(err)
				return err
			}
			_, err = stmt.Exec()
			if err != nil {
				logger.Error(err)
				return err
			}

			return nil
		}
	}
	return errors.New("not found this match info")
}

func CheckFailKey(call_id, nway_flag, content string) error {
	var sql string
	sql = "select id from fail_keys where '" + content + "' ~ keyword"
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	if err == nil {
		for rows.Next() {
			rows.Close()
			if nway_flag == "read" {
				sql = fmt.Sprintf("insert into fail_info(call_id,content,is_read)values('%s','%s',True);", call_id, content)
			} else {
				sql = fmt.Sprintf("insert into fail_info(call_id,content,is_read)values('%s','%s',False);", call_id, content)
			}
			NwayConn = nwayconnection.NewDb()
			stmt, err := NwayConn.GetConn().Prepare(sql)
			defer stmt.Close()
			if err != nil {
				logger.Error(err)
				return err
			}
			_, err = stmt.Exec()
			if err != nil {
				logger.Error(err)
				return err
			}
			return nil
		}
	}
	return errors.New("not found this match info")
}

func CheckFineKey(call_id, nway_flag, content string) error {
	var sql string
	sql = "select id from fine_keys where '" + content + "' ~ keyword"
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	if err == nil {
		for rows.Next() {
			rows.Close()
			if nway_flag == "read" {
				sql = fmt.Sprintf("insert into fine_info(call_id,content,is_read)values('%s','%s',True);", call_id, content)
			} else {
				sql = fmt.Sprintf("insert into fine_info(call_id,content,is_read)values('%s','%s',False);", call_id, content)
			}
			NwayConn = nwayconnection.NewDb()
			stmt, err := NwayConn.GetConn().Prepare(sql)
			defer stmt.Close()
			if err != nil {
				logger.Error(err)
				return err
			}
			_, err = stmt.Exec()
			if err != nil {
				logger.Error(err)
				return err
			}
			return nil
		}
	}
	return errors.New("not found this match info")
}

func QueryByRecordFile(dsr_record_file string) error {
	var sql string

	sql = fmt.Sprintf("SELECT   id   "+
		" FROM dsr_flow_content where record_path='%s';", dsr_record_file)
	//logger.Debug("sql:%s\n", sql)
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	if err == nil {
		for rows.Next() {

			return nil
		}
		err = errors.New("not found cdr info")
	}
	return err
}

// 第二个返回参数为是否强行挂机
func InsertDSRRecord(call_id, nway_flag, dsr_record_file, content string) (error, bool) {
	if QueryByRecordFile(dsr_record_file) == nil {
		return errors.New("has insert a record into dsr_flow_content"), false
	}
	err, fail, fine, must_hangup := getKeyword(content)
	if err != nil {
		return err, false
	}
	var sql string
	if nway_flag == "read" {
		sql = "INSERT INTO dsr_flow_content (content,record_path,caller,callee,dsr_time,call_id,fail,fine)" +
			" VALUES ($1,$2,'true','false',now(),$3,'" + fail + "','" + fine + "');"

	} else {
		sql = "INSERT INTO dsr_flow_content (content,record_path,caller,callee,dsr_time,call_id,fail,fine)" +
			" VALUES ($1,$2,'false','true',now(),$3,'" + fail + "','" + fine + "');"
	}
	logger.Debug(sql)
	NwayConn := nwayconnection.NewDb()
	stmt, err := NwayConn.GetConn().Prepare(sql)
	defer stmt.Close()
	if err != nil {
		logger.Error(err)
		return err, must_hangup
	} else {
		_, err = stmt.Exec(content, dsr_record_file, call_id)
		if err != nil {
			logger.Error(err)
			return err, must_hangup
		}
	}

	if len(content) < 1 {
		return nil, must_hangup
	}
	err = CheckFailKey(call_id, nway_flag, content)

	if err != nil {
		err = CheckSuccessKey(call_id, nway_flag, content)
		if err != nil {
			err = CheckFineKey(call_id, nway_flag, content)
		}
	}
	return err, must_hangup

}

// 第四个返回参数用于，是不是强行挂断
func getKeyword(content string) (error, string, string, bool) {
	var sql string
	var fineSql string
	sql = "select keyword,auto_hangup from fail_keys WHERE  '" + content + "' ~ keyword;"
	fineSql = "select keyword from fine_keys WHERE  '" + content + "' ~ keyword;"
	NwayConn := nwayconnection.NewDb()
	rows, err := NwayConn.GetConn().Query(sql)
	defer rows.Close()
	var fail string
	var fine string
	var auto_hangup bool
	var must_hangup bool = false
	var failfound bool = false
	if err == nil {
		for rows.Next() {
			failfound = true
			var content string
			rows.Scan(&content, &auto_hangup)
			fail += content + ","
			if auto_hangup {
				must_hangup = true
			}
		}
		//都是些禁忌词，就不再找这些fine词
		if failfound == false {
			rowsfine, err := NwayConn.GetConn().Query(fineSql)
			if err == nil {
				for rowsfine.Next() {
					var content string
					rowsfine.Scan(&content)
					fine += content + ","
				}
				return err, fail, fine, must_hangup
			}
		}

		return err, fail, fine, must_hangup
	}

	return err, fail, fine, must_hangup
}
func InsertDSRRuntime(call_id, a_number, b_number string) error {
	var sql = "INSERT INTO runtime (call_id,a_number,b_number)" +
		" VALUES ($1,$2,$3);"

	logger.Debug(sql)
	NwayConn := nwayconnection.NewDb()
	stmt, err := NwayConn.GetConn().Prepare(sql)
	defer stmt.Close()
	if err != nil {
		logger.Error(err)
		return err
	}

	_, err = stmt.Exec(call_id, a_number, b_number)
	if err != nil {
		logger.Error(err)
		return err
	}

	return err

}
func DelectRuntime(uuid string) error {
	var sql = "DELETE FROM runtime WHERE call_id = $1"

	logger.Debug(sql)
	NwayConn := nwayconnection.NewDb()
	stmt, err := NwayConn.GetConn().Prepare(sql)
	defer stmt.Close()
	if err != nil {
		logger.Error(err)
		return err
	}

	_, err = stmt.Exec(uuid)
	if err != nil {
		logger.Error(err)
		return err
	}

	return err
}

// 清理一周前的cdr_pg库里的内容，避免这个库过大
func AutoCleanPGCdr() error {
	var sql string
	for {
		mytime := time.Now()
		if mytime.Hour() == 03 && mytime.Minute() == 03 && (mytime.Second() > 5 && mytime.Second() < 15) {
			//十秒钟处理一次,3.3.5-15秒间处理
			sql = fmt.Sprintf("delete from call_pg_cdr where start_stamp < now() - interval '6 day';")
			logger.Debug(sql)
			NwayConn := nwayconnection.NewDb()
			stmt, err := NwayConn.GetConn().Prepare(sql)
			defer stmt.Close()
			if err != nil {
				logger.Error(err)

			} else {

				_, err = stmt.Exec()
				if err != nil {
					logger.Error(err)

				}
			}
			time.Sleep(9 * time.Second)
		}
		time.Sleep(9 * time.Second)

	}
}
